﻿#region License
/*
Copyright (c) 2010, Dilyan Rusev
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, 
are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list
 of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this 
list of conditions and the following disclaimer in the documentation and/or other 
materials provided with the distribution.

* Neither the name of TU-Sofia nor the names of its contributors may be used to endorse or 
promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS 
OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
 AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
#endregion

using System.Collections.Generic;
using System.Linq;
using System.Text;

using Db = System.Data.SQLite;

namespace DbEngine
{
    /// <summary>
    /// Provides a basic interface for simple, and efficient communication with SQLlite
    /// </summary>
    public class Database
    {
        #region Fields

        private string fileName;
        private readonly string connectionString;

        #endregion

        #region Initialization

        /// <summary>
        /// Initializes a new instance of the <see cref="Database"/> class.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        private Database(string fileName)
        {
            this.fileName = fileName;
            this.connectionString = "Data Source=" + this.fileName;
        }

        /// <summary>
        /// Creates a new database object from a SQLite db file name
        /// </summary>
        /// <param name="fileName">Physical location to the SQLite database file</param>
        /// <returns>Newly created instance.</returns>
        public static Database Open(string fileName)
        {
            return new Database(fileName);
        }

        #endregion

        #region Query

        /// <summary>
        /// Execute a query and receive an enumerable to iterate through the results (if any)
        /// </summary>
        /// <typeparam name="T">Type of the persistent class.</typeparam>
        /// <param name="query">Query to be executed against SQLite</param>
        /// <param name="args">Optional formatting parameters</param>
        /// <returns>Enumerable for looping over the result.</returns>
        public IEnumerable<T> GetQueryResult<T>(string query, params object[] args)
        {
            if (args.Length > 0)
            {
                query = string.Format(query, args);
            }
            var connection = this.GetConnection(true);
            var cmd = new Db.SQLiteCommand(query, connection);           
            var reader = cmd.ExecuteReader();
            return new DbEnumerable<T>(reader);
        }

        /// <summary>
        /// Execute a command query with optional formatting parameters
        /// </summary>
        /// <param name="query">Command query to be executed against SQLite</param>
        /// <param name="args">Optional formatting arguments</param>
        public void ExecuteQueryCommand(string query, params object[] args)
        {
            if (args.Length > 0)
            {
                query = string.Format(query, args);
            }
            var connection = this.GetConnection(true);
            var cmd = new Db.SQLiteCommand(query, connection);
            cmd.ExecuteNonQuery();
        }

        #endregion

        #region CRUD

        /// <summary>
        /// Create a row in the database for a persistent class
        /// </summary>
        /// <typeparam name="T">Persistent type</typeparam>
        /// <param name="instance">Instance with the desired db fields filled in</param>
        public void Create<T>(T instance)
        {
            var def = Mapping.GetDefinition<T>();
            StringBuilder command = new StringBuilder();
            command.Append("INSERT INTO ");
            command.Append('"');
            command.Append(def.DbType.TableName);
            command.Append('"');

            command.Append(" (");
            for (int i = 0; i < def.Fields.Count; i++)
            {
                if (!def.Fields[i].IdentityField)
                {
                    command.Append('"');
                    command.Append(def.Fields[i].FieldName);
                    command.Append('"');
                    if (i + 1 != def.Fields.Count)
                    {
                        command.Append(", ");
                    }
                }
            }

            command.Append(") VALUES (");

            for (int i = 0; i < def.Fields.Count; i++)
            {
                if (!def.Fields[i].IdentityField)
                {
                    command.Append('\'');
                    var clrValue = def.GetPropertyDesc(def.Fields[i].FieldName).GetValue(instance);
                    var sqlValue = Mapping.GetSqlValue(clrValue);
                    command.Append(sqlValue);
                    command.Append('\'');

                    if (i + 1 != def.Fields.Count)
                    {
                        command.Append(", ");
                    }
                }
            }

            command.Append(')');

            this.ExecuteQueryCommand(command.ToString());
        }

        /// <summary>
        /// Update a row in database with the provided information
        /// </summary>
        /// <typeparam name="T">Persistent type</typeparam>
        /// <param name="instance">Instance of a persistent type, whose identity fields to use for updating the db row</param>
        public void Update<T>(T instance)
        {
            var def = Mapping.GetDefinition<T>();
            StringBuilder command = new StringBuilder();
            command.Append("UPDATE ");
            command.Append('"');
            command.Append(def.DbType.TableName);
            command.Append("\" SET ");            
            for (int i = 0; i < def.Fields.Count; i++)
            {
                var dbField = def.Fields[i];
                if (!dbField.IdentityField)
                {
                    command.Append('"');
                    command.Append(dbField.FieldName);
                    command.Append("\"='");
                    var clrVal = def.GetPropertyDesc(dbField.FieldName).GetValue(instance);
                    var sqlVal = Mapping.GetSqlValue(clrVal);
                    command.Append(sqlVal);
                    command.Append("'");
                    if (i + 1 != def.Fields.Count)
                    {
                        command.Append(", ");
                    }
                }
            }
            

            command.Append(" WHERE ");

            var ids = def.Fields.Where(f => f.IdentityField).ToArray();
            for (int i = 0; i < ids.Length; i++)
            {
                command.Append('"');
                command.Append(ids[i].FieldName);
                command.Append("\"='");
                var clrVal = def.GetPropertyDesc(ids[i].FieldName).GetValue(instance);
                var sqlVal = Mapping.GetSqlValue(clrVal);
                command.Append(sqlVal);
                command.Append("'");

                if (i + 1 != ids.Length)
                {
                    command.Append(" AND ");
                }
            }

            this.ExecuteQueryCommand(command.ToString());
        }

        /// <summary>
        /// Get an a persistent class from database using single identity field
        /// </summary>
        /// <typeparam name="T">Type of the persistent class</typeparam>
        /// <param name="id">Value of the only identity field</param>
        /// <returns>Instance of the row with property identity or null</returns>
        public T Get<T>(object id)
        {
            var def = Mapping.GetDefinition<T>();
            var idName = def.Fields.Single(f => f.IdentityField);
            var idVal = Mapping.GetSqlValue(id);
            var result = this.GetQueryResult<T>("SELECT * FROM \"{0}\" WHERE \"{1}\" = '{2}'", def.DbType.TableName, idName, idVal);
            return result.SingleOrDefault();
        }

        /// <summary>
        /// Get all rows that satisfy a condition
        /// </summary>
        /// <typeparam name="T">Persistent type</typeparam>
        /// <param name="whereClause">Condition, or where clause, that all results must satisfy</param>
        /// <returns>Result of the query</returns>
        public IEnumerable<T> GetAllWithCondition<T>(string whereClause)
        {
            var def = Mapping.GetDefinition<T>();
            return this.GetQueryResult<T>("SELECT * FROM \"{0}\" WHERE {1}", def.DbType.TableName, whereClause);
        }

        /// <summary>
        /// Get all rows for the persistent type
        /// </summary>
        /// <typeparam name="T">Persistent type</typeparam>
        /// <returns>All rows of the persistent type's table</returns>
        public IEnumerable<T> GetAll<T>()
        {
            var def = Mapping.GetDefinition<T>();
            return this.GetQueryResult<T>("SELECT * FROM \"{0}\"", def.DbType.TableName);
        }

        /// <summary>
        /// Delete a row by single identity
        /// </summary>
        /// <typeparam name="T">Persistent type name</typeparam>
        /// <param name="id">Single identity value</param>
        public void Delete<T>(object id)
        {
            var def = Mapping.GetDefinition<T>();
            var sqlId = Mapping.GetSqlValue(id);
            var idName = def.Fields.Single(f => f.IdentityField).FieldName;
            this.ExecuteQueryCommand("DELETE FROM \"{0}\" WHERE \"{1}\" = '{2}'", def.DbType.TableName, idName, sqlId);
        }

        #endregion

        #region He1per methods

        /// <summary>
        /// Create a new connection and optionally open it
        /// </summary>
        /// <param name="open">True to open the connection, false to just create it.</param>
        /// <returns>Connection to the database</returns>
        private Db.SQLiteConnection GetConnection(bool open)
        {
            var conn = new Db.SQLiteConnection(this.connectionString);
            if (open)
            {
                conn.Open();
            }
            return conn;
        }

        #endregion
    }
}
